Arithmetic and Comparison Operators in Tableau – Complete Detailed Notes

Topic: Arithmetic and Comparison Operators

Course: JustAcademy Tableau Training

Course Demo: Register for a Tableau Course Demo

Learning Objectives: Understand arithmetic and comparison operators in Tableau, use them in calculated fields, perform business calculations, compare values, create conditional expressions, and apply the results in worksheets and dashboards.

1. Introduction to Operators in Tableau

Operators in Tableau are symbols or keywords that perform operations on values, fields, and expressions. They are important components of calculated fields and help analysts transform data, calculate business metrics, and compare values.

For example, an analyst can use arithmetic operators to calculate profit and revenue growth. Comparison operators can determine whether sales exceed a target, whether profit is positive, or whether a product's discount is greater than a specified percentage.

Tableau calculations can combine fields, functions, operators, and literal values. A field is commonly referenced using square brackets, such as [Sales] or [Profit].

Example

[Sales] - [Cost]

This calculation subtracts cost from sales. If sales are 50,000 and cost is 35,000, the resulting profit is 15,000.

2. What Are Arithmetic Operators?

Arithmetic operators perform mathematical operations on numeric values or numeric fields. They are commonly used to calculate totals, differences, costs, profits, ratios, discounts, and other business metrics.

Common Arithmetic Operators

Note: Tableau's modulo operator is intended for integer calculations. Check the data types of the expressions used in the formula.

3. Addition Operator (+)

The addition operator adds two numeric values or fields. Tableau also supports the plus operator for string concatenation and certain date calculations.

Syntax

[Field 1] + [Field 2]

Example 1: Add Two Numbers

10 + 20

Result: 30

Example 2: Calculate Total Revenue

Suppose a company has two revenue fields: online revenue and store revenue. You can calculate total revenue using:

[Online Revenue] + [Store Revenue]

Example 3: Add a Fixed Amount to Sales

[Sales] + 500

This adds 500 to each applicable sales value. This is useful when modelling a fixed adjustment, provided the adjustment matches the business requirement.

Example 4: Combine Text Values

[First Name] + " " + [Last Name]

This formula combines a first name and last name with a space between them. For example, "Rahul" and "Sharma" become "Rahul Sharma". If either field contains NULL, handle it according to the intended business logic.

4. Subtraction Operator (-)

The subtraction operator calculates the difference between two numeric values. It can also be used with dates and to negate an expression.

Syntax

[Field 1] - [Field 2]

Example 1: Calculate Profit

[Sales] - [Cost]

A negative result indicates that cost exceeds sales for that record, assuming the fields represent comparable amounts.

Example 2: Calculate Remaining Target

[Sales Target] - [Actual Sales]

If the target is 100,000 and actual sales are 75,000, the remaining target is 25,000.

Example 3: Calculate a Variance

[Actual Cost] - [Budgeted Cost]

A positive variance means actual cost is greater than budgeted cost. A negative variance means actual cost is lower than budgeted cost. The interpretation depends on the order in which the fields are subtracted.

Example 4: Calculate the Difference Between Dates

In Tableau, subtracting one date from another can return a difference in days for supported date values.

[End Date] - [Start Date]

For more specific date calculations, functions such as DATEDIFF() may be clearer because they let you specify the date part being compared.

5. Multiplication Operator (*)

The multiplication operator multiplies two numeric values or fields. It is commonly used to calculate line-item totals, extended prices, tax amounts, and discount values.

Syntax

[Field 1] * [Field 2]

Example 1: Calculate Total Price

[Unit Price] * [Quantity]

Example 2: Calculate Tax Amount

If the tax rate is stored as a decimal, such as 0.18 for 18%, calculate tax using:

[Price] * [Tax Rate]

For a price of 1,000 and a rate of 0.18, the tax amount is 180.

Example 3: Calculate a Percentage-Based Bonus

[Sales] * [Bonus Rate]

If sales are 80,000 and the bonus rate is 0.05, the calculated bonus is 4,000.

6. Division Operator (/)

The division operator divides one numeric value by another. It is useful for calculating ratios, average values, unit costs, profit margins, and percentage-based metrics.

Syntax

[Numerator] / [Denominator]

Example 1: Calculate Profit Ratio

SUM([Profit]) / SUM([Sales])

If total profit is 20,000 and total sales are 100,000, the profit ratio is 0.20, or 20% when formatted as a percentage.

Example 2: Calculate Average Price per Unit

[Total Revenue] / [Quantity]

If total revenue is 15,000 and the quantity sold is 300 units, the average revenue per unit is 50.

Example 3: Protect Against Division by Zero

IF [Quantity] != 0 THEN
    [Total Revenue] / [Quantity]
ELSE
    NULL
END

This formula avoids performing the division when quantity is zero. Returning NULL indicates that the result is unavailable or undefined rather than treating it as zero.

Important: Choose the fallback value based on the business meaning. Returning zero, NULL, or a separate label can produce different interpretations in a report.

7. Modulo Operator (%)

The modulo operator returns the remainder after division. In Tableau, the modulo operator is intended for integer calculations.

Syntax

Number1 % Number2

Example 1: Basic Modulo

9 % 2

Result: 1, because 2 goes into 9 four times, leaving a remainder of 1.

Example 2: Identify Even and Odd Numbers

[Employee ID] % 2

A remainder of 0 indicates an even integer, while a remainder of 1 indicates an odd positive integer. To create a category:

IF [Employee ID] % 2 = 0 THEN "Even"
ELSE "Odd"
END

Use this example only when Employee ID contains suitable integer values. IDs are identifiers, not necessarily meaningful numerical measurements.

8. Exponentiation Operator (^)

The exponentiation operator raises a number to a specified power. It is useful for mathematical modelling and certain statistical or analytical calculations.

Syntax

Number ^ Power

Examples

2 ^ 3

Result: 8, because 2 × 2 × 2 = 8.

[Value] ^ 2

This squares each applicable value. For example, if the value is 5, the result is 25.

Tableau also provides mathematical functions such as POWER() for exponentiation.

9. What Are Comparison Operators?

Comparison operators compare two values or expressions and return a logical result. The result is typically TRUE or FALSE, but it can be NULL when the comparison cannot be determined, such as when an operand is NULL.

Comparison operators are commonly used in IF statements, calculated categories, filters, and conditional business rules.

Common Comparison Operators

Tableau supports both = and == for equality, and both != and <> for inequality. For consistency, use one form throughout a workbook.

10. Equal To Operator (= or ==)

The equal-to operator checks whether two values match. It can compare numeric values, text values, and dates of compatible types.

Example 1: Compare Sales to a Target

[Sales] = 10000

The result is TRUE when sales equal 10,000 and FALSE when they do not.

Example 2: Check a Region

[Region] = "West"

This expression checks whether the region value is West.

Example 3: Create a Category

IF [Region] = "West" THEN "Western Region"
ELSE "Other Region"
END

This creates a new category for records in the West region and groups the remaining records as Other Region.

11. Not Equal To Operator (!= or <>)

The not-equal operator checks whether two values are different.

Example 1: Identify Nonzero Profit

[Profit] != 0

This expression returns TRUE when profit is not zero.

Example 2: Exclude a Region in a Calculation

IF [Region] <> "East" THEN [Sales]
ELSE 0
END

This returns sales for records outside the East region and zero for East-region records. Consider whether zero is an appropriate replacement for excluded values in the intended analysis.

12. Greater Than Operator (>)

The greater-than operator checks whether the left value is strictly greater than the right value.

Example

[Sales] > 10000

This expression is TRUE when sales exceed 10,000. A value of exactly 10,000 returns FALSE.

Business Example: High-Value Sales

IF [Sales] > 10000 THEN "High Sales"
ELSE "Standard Sales"
END

This calculation classifies records with sales above 10,000 as High Sales.

13. Less Than Operator (<)

The less-than operator checks whether the left value is strictly smaller than the right value.

Example

[Inventory] < 20

This returns TRUE when inventory is below 20.

Business Example: Low Inventory Alert

IF [Inventory] < 20 THEN "Reorder Required"
ELSE "Stock Available"
END

This calculation identifies records where inventory falls below the chosen threshold.

14. Greater Than or Equal To Operator (>=)

The greater-than-or-equal-to operator checks whether a value is greater than or exactly equal to another value.

Example

[Sales] >= 10000

The expression returns TRUE when sales are 10,000 or higher.

Business Example: Target Achievement

IF [Actual Sales] >= [Sales Target] THEN "Target Achieved"
ELSE "Target Not Achieved"
END

This formula checks whether actual sales meet or exceed the sales target.

15. Less Than or Equal To Operator (<=)

The less-than-or-equal-to operator checks whether a value is smaller than or exactly equal to another value.

Example

[Discount Rate] <= 0.10

This returns TRUE when the discount rate is at most 10%, assuming the rate is stored as a decimal.

Business Example: Low-Discount Products

IF [Discount Rate] <= 0.10 THEN "Low Discount"
ELSE "High Discount"
END

Verify the source format before applying the condition. A dataset that stores 10 for 10% requires a different comparison from one that stores 0.10.

16. Combining Arithmetic and Comparison Operators

Arithmetic and comparison operators are often used together in a single calculated field. Arithmetic operators calculate a value, and comparison operators test that result against a condition.

Example 1: Calculate Profit and Check Profitability

[Sales] - [Cost] > 0

This expression checks whether sales minus cost is greater than zero.

Example 2: Calculate a Discounted Price and Compare It With a Budget

[Price] * (1 - [Discount Rate]) <= [Budget]

This calculates the discounted price and checks whether it is within the budget. The discount rate should be stored as a decimal, such as 0.20 for 20%.

Example 3: Identify Sales Above a Target

[Sales] - [Sales Target] > 0

This checks whether sales exceed the target. A positive result means the target has been exceeded.

Example 4: Check Whether a Profit Ratio Meets a Minimum

IF SUM([Profit]) / SUM([Sales]) >= 0.15 THEN "Healthy Margin"
ELSE "Review Margin"
END

This compares the aggregated profit ratio with a 15% threshold. Ensure that the denominator is not zero and that the ratio is defined at the intended level of aggregation.

17. Understanding Operator Precedence

Operator precedence determines the order in which Tableau evaluates the parts of a calculation. If a formula contains multiple operators, Tableau follows its defined precedence rules unless parentheses change the order.

General Order of Precedence

Example 1: Multiplication Before Addition

2 + 3 * 4

Result: 14, because multiplication is evaluated before addition.

Example 2: Use Parentheses to Change the Order

(2 + 3) * 4

Result: 20, because the expression inside parentheses is evaluated first.

Example 3: Compare a Calculated Value

[Sales] - [Cost] > 5000

Tableau calculates the subtraction before evaluating the comparison. Parentheses can make complex formulas easier to read:

([Sales] - [Cost]) > 5000

18. Using Parentheses in Calculations

Parentheses make formulas easier to understand and allow you to specify which operations should be evaluated first. Expressions inside the innermost parentheses are evaluated before expressions outside them.

Example: Calculate Discounted Price

[Price] * (1 - [Discount Rate])

If price is 1,000 and the discount rate is 0.20, the formula becomes 1,000 × (1 - 0.20), producing 800.

Example: Calculate a Sales Difference Percentage

([Current Sales] - [Previous Sales]) / [Previous Sales]

This calculates the difference between current and previous sales, then divides it by previous sales. Handle a previous sales value of zero before using this formula.

19. Handling NULL Values in Comparisons

A NULL value represents missing or unknown information. A comparison involving NULL may return NULL rather than TRUE or FALSE. For example, [Sales] > 1000 cannot determine whether sales exceed 1,000 if sales is NULL.

Example: Check for Missing Sales

IF ISNULL([Sales]) THEN "Missing Sales"
ELSEIF [Sales] >= 10000 THEN "High Sales"
ELSE "Other Sales"
END

This formula handles missing sales values explicitly before applying the numeric comparison.

Best Practices for NULL Values

• Do not assume that NULL is equivalent to zero.
• Use ISNULL() when you need to identify missing values.
• Decide whether missing values should remain NULL, be replaced, or receive a separate category.
• Test calculations against records containing missing values.

20. Creating Calculated Fields Using Operators

Follow these steps to create a calculated field using arithmetic and comparison operators.

1. Open Tableau and connect to a data source.
2. Open a worksheet.
3. Select Analysis > Create Calculated Field.
4. Enter a meaningful name, such as Profit Status.
5. Enter the formula.

IF [Sales] - [Cost] > 0 THEN "Profitable"
ELSEIF [Sales] - [Cost] = 0 THEN "Break Even"
ELSE "Loss"
END

6. Check the calculation for syntax errors and invalid field references.
7. Click OK to save the field.
8. Drag the calculated field into a worksheet, such as Color or Rows.
9. Check the resulting categories against sample records.

21. Practical Project: Retail Sales Performance Analysis

Scenario: A retail business wants to evaluate product sales, calculate profit, and identify whether sales targets have been achieved.

Sample Data

Task 1: Calculate Profit

[Sales] - [Cost]

Task 2: Calculate Target Difference

[Sales] - [Sales Target]

Task 3: Create Target Status

IF [Sales] >= [Sales Target] THEN "Target Achieved"
ELSE "Below Target"
END

Expected Results

Task 4: Build the Visualization

1. Drag Product to Rows.
2. Drag Sales to Columns.
3. Use the Target Status calculated field on Color.
4. Add Profit and Target Difference to Tooltip.
5. Check whether each product meets its target and whether it is profitable.

Learning Outcome: This project demonstrates how arithmetic operators calculate values and comparison operators classify those values for business reporting.

22. Common Mistakes and Troubleshooting

23. Best Practices

• Use meaningful names for calculated fields.
• Keep calculations readable and use parentheses for complex formulas.
• Confirm the data types of all fields before applying operators.
• Check whether thresholds should use > or >=, and similarly for less-than comparisons.
• Handle NULL values and zero denominators explicitly.
• Test calculations with positive, negative, zero, and boundary values.
• Use consistent percentage conventions throughout the workbook.
• Validate whether the calculation is row-level or aggregate-level.
• Verify the results in a worksheet before publishing a dashboard.

24. Interview Questions and Answers

Q1. What are arithmetic operators in Tableau?

Answer: Arithmetic operators perform mathematical operations, such as addition, subtraction, multiplication, division, modulo, and exponentiation.

Q2. What are comparison operators?

Answer: Comparison operators compare two values and return a logical result, generally TRUE or FALSE, or NULL when the result cannot be determined.

Q3. What is the difference between > and >=?

Answer: The greater-than operator returns TRUE only when the first value is larger. The greater-than-or-equal-to operator also returns TRUE when both values are equal.

Q4. How do you calculate profit using Tableau operators?

Answer: Use [Sales] - [Cost], assuming profit is defined as sales minus cost.

Q5. How do you calculate a discounted price?

Answer: If the discount rate is stored as a decimal, use [Price] * (1 - [Discount Rate]).

Q6. What does the modulo operator do?

Answer: The modulo operator returns the remainder of a division operation for supported integer calculations.

Q7. What is operator precedence?

Answer: Operator precedence defines the order in which operators are evaluated. Multiplication and division generally occur before addition and subtraction.

Q8. Why are parentheses useful?

Answer: Parentheses explicitly control the order of operations and make complex formulas easier to understand.

Q9. Can comparison operators compare text?

Answer: Yes. Comparison operators can compare compatible text values, such as checking whether [Region] = "West".

Q10. What happens when a comparison includes NULL?

Answer: The result may be NULL because the comparison cannot be determined. Use functions such as ISNULL() to handle missing values explicitly.

Q11. How can you check whether sales meet a target?

Answer: Use [Sales] >= [Sales Target] to return TRUE when sales meet or exceed the target.

Q12. What is the difference between = and !=?

Answer: The equal-to operator checks whether values match, while the not-equal operator checks whether they differ.

25. Practice Exercises

1. Create a calculated field to add Online Sales and Store Sales.
2. Calculate profit using Sales minus Cost.
3. Calculate the total price using Unit Price multiplied by Quantity.
4. Calculate a discounted price using a decimal discount rate.
5. Create a condition that checks whether sales are greater than 25,000.
6. Classify products as High Sales when sales are at least 10,000.
7. Identify products whose profit is less than zero.
8. Create a Target Status field using Sales and Sales Target.
9. Use parentheses to calculate a percentage change correctly.
10. Test how your calculations behave when values are zero or NULL.

26. Quick Revision Checklist

• Understand the purpose of arithmetic and comparison operators.
• Know how to use +, -, *, and /.
• Understand modulo and exponentiation.
• Use equality and inequality operators correctly.
• Distinguish strict comparisons from inclusive comparisons.
• Understand operator precedence and parentheses.
• Combine arithmetic and comparison operators in calculated fields.
• Handle zero denominators and NULL values.
• Test calculated results against known examples.
• Apply calculated fields in Tableau worksheets and dashboards.

27. Conclusion

Arithmetic and comparison operators are fundamental tools for creating useful Tableau calculations. Arithmetic operators help calculate values such as profit, discounted prices, totals, and ratios. Comparison operators help test conditions, identify target achievement, and classify records.

By combining these operators with IF statements, correct aggregation, parentheses, and appropriate NULL handling, you can create reliable calculated fields that support meaningful analysis and business decisions.

28. Tableau Training and Course Demo

Continue learning Tableau through the following course resources:

• JustAcademy Tableau Training
• Register for a Tableau Course Demo

29. Official Tableau Learning Resources

• Tableau Official Documentation: Writing Calculations and Operators
• Tableau Official Documentation: Logical Functions
• Tableau: Getting Started with Calculations